﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.Odbc;
using System.Configuration;
using shared_libs;


namespace chatc_server.database_methods.Database_interface
{
    class database_interface
    {
        private static Dictionary<String, IMessageReciever> online_recs = new Dictionary<String, IMessageReciever>();
       
       
        
        /// <summary>
        /// This method adds a user to the users database
        /// notice we don't sanitize any of our inputs!
        /// </summary>
        /// <param name="username">the username of the client</param>
        /// <param name="password">and its password</param>
        /// <returns>true: insert succes!, false: insert failed!</returns>
        /// 

        
        public static Boolean addNewUser(String username, String password)
        {
            //string ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\\Users\\Armen Nazarian\\school\\DNP\\project\\chatc\\system\\chatc\\WebApplication1\\App_Data\\Test.mdb";
            String ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();


            using (OdbcConnection conn = new OdbcConnection(ConnectionString))
            {
                try
                {

                    // Open the connection
                    conn.Open();

                    // Create a command object, and tie it to the connection object
                    String insertCommand = "INSERT into users values('" + username + "', '" + password + "', 'n')";
                    OdbcCommand cmd = new OdbcCommand(insertCommand, conn);
                    
                    // excuting the insert command
                    cmd.ExecuteNonQuery();
                    
                    Console.WriteLine("added {0}", username);
                    return true;
                }
                catch (Exception e)
                {

                    Console.WriteLine(e.ToString());
                    return false;
                }

            }
        }
        /// <summary>
        /// This class generates a string of people who are online at the moment.
        /// If and remoteexception is thrown it will mean that the user went offline
        /// and it is handled by deleting the user from the online List where online people are
        /// and updates the database to set the user to offline.
        /// </summary>
        /// <returns>A string that has the username of people who are online, aggregated and
        /// a break line in between</returns>
        public static String listOnlinePeople()
        {
            List<String> usersToBeDel = new List<String>();
            String result = "";
            
            foreach (KeyValuePair<String, IMessageReciever> pair in online_recs)
            {
                try
                {
                    
                    //aggreates the usernames of the people who are online unto a string
                    result +=pair.Key + "\r\n";
                    
                }
                catch (Exception e)
                {
                    //if an exception is thrown it means the remote object is not accessable so
                    //the folowing 10-15 lines deletes the user from the onlinelist
                    Console.WriteLine("delete user");
                    usersToBeDel.Add(pair.Key);
                    
                }

            }

            foreach(String client in usersToBeDel){
                online_recs.Remove(client);
            }


           



            return result;
        }

        /// <summary>
        /// This method adds a user to the onlineList and updates the database so the user now is online
        /// </summary>
        /// <param name="username">username of the user</param>
        /// <param name="password">self-explanatory</param>
        /// <param name="MsgRecClient">The remote object that the server uses to send message and get other
        /// information about the user</param>
        /// <returns></returns>
        public static Boolean logon(String username, String password, IMessageReciever MsgRecClient)
        {

            String ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
            Boolean result = false;

            using (OdbcConnection conn = new OdbcConnection(ConnectionString))
            {
                try
                {
                    if (ValidUsernamePassword(username, password))
                    {
                        conn.Open();
                        //for more security you should have username and password in where clause
                        String updateCommand = "UPDATE users SET online='y' WHERE username ='" + username + "';";
                        
                        OdbcCommand cmd = new OdbcCommand(updateCommand, conn);

                        cmd.ExecuteNonQuery();
                        online_recs.Add(MsgRecClient.getUsername(), MsgRecClient);
                        
                        result = true;
                        
                        Console.WriteLine("{0} is online now", username);

                    }
                    else
                    {
                        Console.WriteLine("wrong username and pass");
                        result = false;
                    }

                    return result;
                }
                catch (Exception e)
                {

                    Console.WriteLine(e.ToString());
                    return false;
                }
            }









        }

        /// <summary>
        /// This methods sends messages to all users who are online. It used the remote object
        /// IMessageReciever that every client has and the object is given to this database upon
        /// logon
        /// </summary>
        /// <param name="FromUser">Who the message is from</param>
        /// <param name="Message">self-explanatory</param>
        public static void sendMessage(String FromUser, String Message)
        {
            List<String> usersToBeDel = new List<String>();

            foreach (KeyValuePair<String, IMessageReciever> pair in online_recs)
            {
                try
                {
                    //uses the remote object of the client to send message to it
                    //a simple version of the Observer pattern
                    pair.Value.receiveMessage(FromUser, Message);



                }
                catch (Exception e)
                {
                    //deteling like the previous method 
                    usersToBeDel.Add(pair.Key);



                }


                foreach (String client in usersToBeDel)
                {
                    SetUserOffline(client);
                    online_recs.Remove(client);

                }

            }
        }


        /// <summary>
        /// This method is used internal by the other methods to check whether or not
        /// a users input for loging in is valid. Checking the username and password combo
        /// NOTICE: We dont santize the input for SQL injections and so on... :)
        /// </summary>
        /// <param name="username">self-explanatory</param>
        /// <param name="password">self-explanatory</param>
        /// <returns>This method returns a boolean, true if the login information is valid and false if not</returns>
        private static Boolean ValidUsernamePassword(string username, string password)
        {
            String ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
            Boolean result = false;

            using (OdbcConnection conn = new OdbcConnection(ConnectionString))
            {
                try
                {

                    // Open the connection
                    conn.Open();

                    // Create a command object, and tie it to the connection object
                    //String insertCommand = "INSERT into users values('" + username + "', '" + password + "', 'n')";
                    String selectCommand = "SELECT count(*) from users where username = '" + username + "' AND password='" + password + "';";

                    OdbcCommand cmd = new OdbcCommand(selectCommand, conn);

                    int count = (int)cmd.ExecuteScalar();
                    if (count == 1)
                    {
                        result = true;
                    }
                    return result;
                }
                catch (Exception e)
                {

                    Console.WriteLine(e.ToString());
                    return false;
                }
            }
        }

        /// <summary>
        /// This method updates the database by setting the 'online' column to 'n' if
        /// a user is offline
        /// </summary>
        /// <param name="username"></param>
        private static void SetUserOffline(String username) {
            String ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();


            using (OdbcConnection conn = new OdbcConnection(ConnectionString))
            {
                try
                {

                    // Open the connection
                    conn.Open();

                    // Create a command object, and tie it to the connection object
                    String insertCommand = "UPDATE users SET online='n' WHERE username='" + username + "';";
                    OdbcCommand cmd = new OdbcCommand(insertCommand, conn);

                    cmd.ExecuteNonQuery();

                    
                   
                }
                catch (Exception e)
                {

                    Console.WriteLine(e.ToString());
                    
                }

            }
        }


        /// <summary>
        /// This method deletes offline user by the checking the onlineList. If an exception is thrown
        /// because of the remoteobject, this will mean the user is offline. Then we delete
        /// </summary>
        public static void deleteOfflines()
        {
            List<String> usersToBeDel = new List<String>();

            foreach (KeyValuePair<String, IMessageReciever> pair in online_recs)
            {
                try
                {

                    pair.Value.getUsername();


                }
                catch (Exception e)
                {

                    usersToBeDel.Add(pair.Key);



                }
            }
            foreach (String client in usersToBeDel)
            {
                SetUserOffline(client);
                online_recs.Remove(client);

            }
            
        }
    }
}

